set echo off
declare 
	user_id1 pls_integer :=user_id_seq.nextval;
	user_id2 pls_integer :=user_id_seq.nextval;

	
BEGIN 

	DBMS_RANDOM.seed (val => TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF') );

	insert into user_info ( user_id, account, passwordhash, pass_eff_dt , pass_exp_dt, addr_id,  addr_detail ) 
	values( user_id1 , '13301231234','NWE1ZjRjYjg5ZmNjMzU3MmJmNmM2MTc2YWIxNDdlYmQ2ZTgxNjYxOGIwYzQ3OWU1ZDRmOWNkMTYyZGE5NTljNA==',
									to_date('2016/12/15 01:02:03','yyyy/mm/dd hh24:mi:ss'),
									to_date('2017/12/15 03:02:01','yyyy/mm/dd hh24:mi:ss'),			1234, '无名街')
	;
	
	insert into user_info ( user_id, account, passwordhash , pass_eff_dt,  pass_exp_dt, addr_id,  addr_detail ) 
	values( user_id2 , '13001231234','N2MxYTc4ZGFlOThlNTkyMjhjNzk2YjdiOTEwMTFiZDQ2MzhmNDEzZWMwZmY1ZGYzZDVjNGFjMDgzOGQ3NTFiZA',
									to_date('2016/12/17 02:03:04','yyyy/mm/dd hh24:mi:ss'),
									to_date('2017/12/17 04:03:02','yyyy/mm/dd hh24:mi:ss'), 2345, '有名坊' );

	------------------  user 
	

	INSERT INTO pond_info (  pond_id   
							,pond_name  
							,user_id 
							,area      
							,height    
							,prod      
							,density   
							,built_dt 
	) 
	select    pond_id_seq.nextval
			 ,'Fishing Pond-'||to_char( mod(user_id*13+pn*37, 97),'fm99')  
			 , user_id  
			 , DBMS_RANDOM.value(low => 30, high => pn * 50)
			 , DBMS_RANDOM.value(low => 3, high => pn * 5)
			 , case when DBMS_RANDOM.value(low =>1, high =>10 ) > 5 then  'Fish' else 'Shrimp' end  
			 , DBMS_RANDOM.value(low => .01, high => 0.5)
			 , trunc(sysdate - DBMS_RANDOM.value(low => 100, high => 200))
	from ( 
		select distinct user_id, level pn 
			 from user_info  
		where user_id  in (user_id1, user_id2)
		connect by level <= 2 
		)
	;
	

	DBMS_OUTPUT.PUT_LINE('Inserted into table pond_info, row count:'||sql%rowcount);
	
	INSERT INTO device_list ( device_id
			 ,pond_id
			 ,device_sn 
						 )
	SELECT  rpad( 'TDE_DEV_'||to_char( mod(pond_id*pn*13,47),'fm99')||'_'||pn||'_'||round(DBMS_RANDOM.value(low => 1, high => 9) ,0),15, 'Y')
			,pond_id , device_list_sn_seq.nextval
	FROM ( select distinct pond_id, level pn
           from pond_info
		   where user_id in ( user_id1, user_id2)
			CONNECT BY LEVEL <=4
			 
		);
	
	DBMS_OUTPUT.PUT_LINE('Inserted into table device_list, row count:'||sql%rowcount);
	
	
	--COMMIT;
END;
/				
set echo on 
